<?php


namespace services\common;


use common\models\base\PatientDiseases;
use common\models\base\Profession;
use common\models\clinic\Clinic;
use common\models\clinic\UserTreatmentDetail;
use common\models\hospital\ClinicName;
use common\models\hospital\HistoryAddress;
use common\models\hospital\ImageLog;
use common\models\marketing\SourceChannel;
use common\models\member\Attr;
use common\models\member\Member;
use Yii;
use common\helpers\CommonHelper;
use common\models\base\Project;
use common\models\hospital\RegistrationList;
use common\models\hospital\SectionList;
use common\models\patient\ConsumptionDetail;
use common\models\patient\OperateLog;
use common\models\clinic\UserTreatment;
use common\models\hospital\Expense;
use common\models\patient\Consumption;
use common\models\patient\HistoryRecord;
use common\models\patient\RevisitingRecord;
use common\models\hospital\HospitalRecord;
use common\models\hospital\InHospital;
use common\models\hospital\InHospitalRecord;
use common\models\patient\User;
use common\models\patient\UserAttr;
use common\components\Service;
use yii\db\Exception;

class ExcelExtendService extends Service
{

    /**
     * @param $hospital
     * @param null $start_time
     * @param null $end_time
     * @throws \Exception
     * @author loujie
     * @apiName  同步入院出院记录表
     * @apiGroup
     * @apiParam {Number}
     * @apiParam {String}
     */
    public static function inHospitalSynchronous($hospital, $start_time = null, $end_time = null)
    {
        if (empty($start_time)) {
            $start_time = date('Y-m-d') . ' 00:00:00';
        }

        if (empty($end_time)) {
            $end_time = date('Y-m-d') . ' 23:59:59';
        }

        $project_id = ExcelService::getProject($hospital);
        $start_time = strtotime($start_time);
        $end_time = strtotime($end_time);

        $hospitalArray = InHospital::find()
            ->where(['between', 'event_time', $start_time, $end_time])
            ->andWhere(['project_id' => $project_id])
            ->asArray()
            ->all();

        // 查询已经录入的,入院出院信息
        $hospitalRecord = HospitalRecord::find()
            ->where(['between', 'in_hospital_time', $start_time, $end_time])
            ->andWhere(['project_id' => $project_id])
            ->asArray()
            ->all();

        // 入院记录
        $inHospitalId = [];
        // 出院记录
        $outHospitalId = [];

        foreach ($hospitalRecord as $item) {
            $item['type'] == InHospital::TYPE_IN ? $inHospitalId[] = $item['in_hospital_serial'] : $outHospitalId[] = $item['in_hospital_serial'];
        }

        $insertData = [];
        // 患者新加的入院次数
        $patient_record = [];

        foreach ($hospitalArray as $item) {

            // 入院处理
            if ($item['type'] == InHospital::TYPE_IN) {

                // 已经录入过的 serial_no 跳过
                if (in_array($item['serial_no'], $inHospitalId)) {
                    continue;
                }

                isset($patient_record[$item['card_id']]) ? $patient_record[$item['card_id']]['count']++ : $patient_record[$item['card_id']]['count'] = 1;

            } else { // 出院处理
                // 已经录入过的 serial_no 跳过
                if (in_array($item['serial_no'], $outHospitalId)) {
                    continue;
                }
            }
            $clinic_id = 0;

            if (!empty($item['clinic_name'])) {
                $sectionInfo = ExcelService::getRedisSection($hospital, $item['clinic_name']);
                $clinic_id = ExcelService::getSection($sectionInfo[$item['clinic_name']], $hospital, 'clinic_id');
            }

            // 住院信息没有的话,从白友查询挂号所在诊室
            if (empty($clinic_id)) {
                $userModel = User::find()
                    ->where([
                        'and',
                        ['project_id' => $project_id],
                        ['status' => User::STATUS_ENABLE],
                        ['patient_card_id' => $item['card_id']]
                    ])
                    ->one();
                $clinic_id = $userModel->clinic_id ?? 0;
            }

            $type = $item['type'] == InHospital::TYPE_IN ? RevisitingRecord::TYPE_IN_HOSPITAL : RevisitingRecord::TYPE_OUT_HOSPITAL;

            self::toHospitalRecord($project_id, $type, $item['event_time'], $item['card_id'], $clinic_id, $item['id']);

            $insertData[] = [
                'project_id' => $item['project_id'],
                'patient_card_id' => $item['card_id'],
                'in_hospital_time' => $item['event_time'],
                'in_hospital_serial' => $item['serial_no'],
                'type' => $item['type'],
                'created_at' => time(),
                'updated_at' => time(),
            ];
        }

        $transaction = Yii::$app->db->beginTransaction();

        try {
            // 查询用户所有住院次数
            $patientCardIds = array_column($hospitalArray,'card_id');
            $inHospitalCount = InHospital::find()
                ->select(['count(id) as count','card_id'])
                ->where([
                    'project_id' => $project_id,
                    'card_id' => $patientCardIds,
                    'type' => InHospital::TYPE_IN
                ])
                ->groupBy('card_id')
                ->indexBy('card_id')
                ->asArray()
                ->all();

            foreach ($patient_record as $patient_card_id => $count) {

                $patientArray = User::find()
                    ->select('u.patient_card_id,u.id,attr.id as aid')
                    ->alias('u')
                    ->leftJoin(UserAttr::tableName() . 'attr', ' attr.patient_user_id = u.id')
                    ->where([
                        'u.project_id' => $project_id,
                        'u.status' => User::STATUS_ENABLE,
                        'u.patient_card_id' => $patient_card_id
                    ])
                    ->asArray()
                    ->one();

                // 用户不存在直接跳过
                if (empty($patientArray)) {
                    continue;
                }

                if (empty($patientArray['aid'])) {
                    $attrModel = new UserAttr();
                    $attrModel->patient_user_id = $patientArray['id'];
                    $attrModel->in_hospital_num = 0;
                } else {
                    $attrModel = UserAttr::findOne($patientArray['aid']);
                }

                $attrModel->in_hospital_num = $inHospitalCount[$patient_card_id]['count'] ?? 0;
                $attrModel->save();
            }

            // 同步,住院/出院记录
            $insertItem = [
                'project_id', 'patient_card_id', 'in_hospital_time', 'in_hospital_serial', 'type', 'created_at', 'updated_at'
            ];
            Yii::$app->db->createCommand()->batchInsert(HospitalRecord::tableName(), $insertItem, $insertData)->execute();

            // 同步住院状态
            self::inHospitalStatus($hospital);

            $transaction->commit();
        } catch (\Exception $e) {

            $transaction->rollBack();
        }

    }

    /**
     * @apiName 更新入院状态
     * @apiGroup
     * @apiParam {Number}
     * @apiParam {String}
     * @param $hospital
     * @author loujie
     */
    public static function inHospitalStatus($hospital)
    {
        $project_id = ExcelService::getProject($hospital);
        // 获取当前最近一次状态是住院还是出院
        $inHospitalSql = InHospital::find()
            ->where(['project_id' => $project_id])
            ->andWhere(['<', 'event_time', time()])
            ->orderBy(['event_time' => SORT_DESC])
            ->limit(10000)
            ->createCommand()
            ->getRawSql();

        $sql = 'select *from (' . $inHospitalSql . ') as a  group by card_id';
        $data = Yii::$app->db3->createCommand($sql)->queryAll();
        $inHospitalArray = [];
        $outHospitalArray = [];

        // 将患者诊疗号,转为attr表 id
        $patientIds = User::find()
            ->select('u.patient_card_id, attr.id')
            ->alias('u')
            ->leftJoin(UserAttr::tableName() . ' attr', 'attr.patient_user_id = u.id')
            ->where([
                'u.project_id' => $project_id,
                'u.status' => User::STATUS_ENABLE,
                'u.patient_card_id' => array_column($data, 'card_id')
            ])->asArray()->all();
        $patientIds = array_column($patientIds, 'id', 'patient_card_id');

        //判断当前
        foreach ($data as $item) {

            $attrId = $patientIds[$item['card_id']] ?? '';
            if (empty($attrId)) {
                continue;
            }
            $item['type'] == InHospital::TYPE_IN ? $inHospitalArray[] = $attrId : $outHospitalArray[] = $attrId;
        }

        // 更新住院
        UserAttr::updateAll(['in_hospital_status' => UserAttr::STATUS_IN_HOSPITAL], ['id' => $inHospitalArray]);
        // 更新出院
        UserAttr::updateAll(['in_hospital_status' => UserAttr::STATUS_OUT_HOSPITAL], ['id' => $outHospitalArray]);
    }

    #### 修复,之前到诊,下次复诊脚本
    public static function revisitingRepair()
    {
        $sql = 'SELECT id FROM ' . User::tableName() . ' WHERE `next_revisiting_time` < `last_revisiting_time` AND next_revisiting_time != 0';
        $userIds = Yii::$app->db->createCommand($sql)->queryAll();
        $userIds = array_column($userIds, 'id');
        User::updateAll(['next_revisiting_time' => 0], ['id' => $userIds]);
        return true;
    }

    /**
     * @apiName 修复5月1 之后的数据
     * @param $hospital
     * @return bool
     * @throws \Exception
     * @author loujie
     */
    public static function toHospitalRepair($hospital)
    {
        Expense::$pix = $hospital;
        $project_id = ExcelService::getProject($hospital);

        //查询消费记录

        // 消费记录
        $expenseArray = Expense::find()
            ->select('id, card_id, event_time, day, section')
            ->where([
                'and',
                ['>', 'event_time', '2020-05-01 00:00:00']
            ])
            ->groupBy('card_id,event_time')
            ->asArray()
            ->all();

        foreach ($expenseArray as $item) {
            // 产品经理说消费诊室没有白友对应的,不记录
            $clinic_id = ExcelService::getSection($item['section'], $hospital, 'clinic_id');
            if (empty($clinic_id)) {
                continue;
            }
            self::toHospitalRecord($project_id, RevisitingRecord::TYPE_CONSUME, strtotime($item['event_time']), $item['card_id'], $clinic_id, $item['id']);
        }

        // 住院/出院记录
        $hospitalArray = InHospital::find()
            ->where([
                'and',
                ['project_id' => $project_id],
                ['>', 'day', '2020-05-01 00:00:00']
            ])
            ->asArray()
            ->all();

        foreach ($hospitalArray as $item) {

            $clinic_id = 0;

            if (!empty($item['clinic_name'])) {
                $sectionInfo = ExcelService::getRedisSection($hospital, $item['clinic_name']);
                $clinic_id = ExcelService::getSection($sectionInfo[$item['clinic_name']], $hospital, 'clinic_id');
            }

            // 住院信息没有的话,从白友查询挂号所在诊室
            if (empty($clinic_id)) {
                $userModel = User::find()
                    ->where([
                        'and',
                        ['project_id' => $project_id],
                        ['status' => User::STATUS_ENABLE],
                        ['patient_card_id' => $item['card_id']]
                    ])
                    ->one();
                $clinic_id = $userModel->clinic_id ?? 0;
            }

            $type = $item['type'] == InHospital::TYPE_IN ? RevisitingRecord::TYPE_IN_HOSPITAL : RevisitingRecord::TYPE_OUT_HOSPITAL;
            self::toHospitalRecord($project_id, $type, $item['event_time'], $item['card_id'], $clinic_id, $item['id']);
        }

        // 治疗记录
        $treatmentArray = UserTreatment::find()
            ->select('id,patient_card_id, recently_clinic_time, clinic_id')
            ->where([
                'and',
                ['project_id' => $project_id],
                ['type' => UserTreatment::TYPE_TREAT],
                ['>', 'recently_clinic_time', strtotime('2020-05-01')]
            ])
            ->asArray()
            ->all();

        foreach ($treatmentArray as $item) {
            self::toHospitalRecord($project_id, RevisitingRecord::TYPE_TREAT, $item['recently_clinic_time'], $item['patient_card_id'], $item['clinic_id'], $item['id']);
        }

        return true;
    }

    /**
     *  查询是否在住院期间内
     * @param $project_id
     * @param $patient_card_id
     * @param $time 时间戳 $time是消费/治疗 时间
     * @return bool
     */
    public static function isHospital($project_id, $patient_card_id, $time)
    {
        $inHospital = InHospital::find()
            ->where([
                'and',
                ['project_id' => $project_id],
                ['card_id' => $patient_card_id],
                ['<', 'event_time', $time]
            ])
            ->orderBy(['event_time' => SORT_DESC])
            ->asArray()
            ->one();

        // 没有住院记录不在住院状态中
        if (!$inHospital) {
            return false;
        }

        // 最近一次,在住院
        if ($inHospital['type'] == InHospital::TYPE_IN) {
            return true;
        }

        return false;
    }

    /**
     * @apiName 添加来院记录
     * @param $project_id
     * @param int $type 来院类型
     * @param int $time 时间戳精确到 秒
     * @param $patient_card_id
     * @param int $clinic_id 诊室id
     * @param int $source_id 来源id
     * @return bool
     * @throws \Exception
     * @author loujie
     */
    public static function toHospitalRecord($project_id, $type, $time, $patient_card_id, $clinic_id, $source_id)
    {
        $day = strtotime(date('Y-m-d', $time));
        // 查询该患者当天所有记录
        $dayArray = RevisitingRecord::find()
            ->where([
                'and',
                ['project_id' => $project_id],
                ['day_time' => $day],
                ['patient_card_id' => $patient_card_id]
            ])
            ->asArray()
            ->all();

        $is_sum = 0;
        $data = [];

        foreach ($dayArray as $item) {
            $data[$item['type']]['time'][] = $item['revisting_time'];
            $data[$item['type']]['id'][] = $item['source_id'];
            $item['is_sum'] == RevisitingRecord::IS_SUM_YES ? $is_sum = 1 : '';
        }

        $this_sum = RevisitingRecord::IS_SUM_NO;
        $is_in_Hospital = self::isHospital($project_id, $patient_card_id, $time);

        // 住院期间,不统计来院次数
        if ($is_sum == 0 && (!$is_in_Hospital || $type == RevisitingRecord::TYPE_IN_HOSPITAL)) {
            $this_sum = RevisitingRecord::IS_SUM_YES;
        }

        // 出院记录一律 为0
        if ($type == RevisitingRecord::TYPE_OUT_HOSPITAL) {
            $this_sum = RevisitingRecord::IS_SUM_NO;
        }

        // 判断同类型,相同时间的跳过,相同的来源id 跳过
        if (isset($data[$type]) && (in_array($time, $data[$type]['time']) || in_array($source_id, $data[$type]['id']))) {
            return false;
        }

        // 没有同类型同 时间的插入
        $model = new RevisitingRecord();
        $model->project_id = $project_id;
        $model->patient_card_id = $patient_card_id;
        $model->revisting_time = $time;
        $model->clinic_id = $clinic_id;
        $model->source_id = $source_id;
        $model->type = $type;
        $model->is_sum = $this_sum;
        $model->day_time = $day;
        $model->created_at = time();
        $model->save();

        // 如果存在有效来院次数 同步用户的来院次数,取最新的上次到诊时间
        if ($this_sum == 1) {

            $params = [
                'project_id' => $project_id,
                'clinic_id' => $clinic_id,
                'patient_card_id' => $patient_card_id,
                'month' => $time,
            ];
            // 月有效次数
            Yii::$app->services->complexReport->savePatientMonthRevisitRecord($params);
            // 更新来院次数,下次复诊时间,未到院天数
            self::updateLastRevisitingTime($project_id, $patient_card_id, $time);

            // 上次到诊日期为当天，下次回访日期也为当天，下次回访日期延后一周【撤销的治疗等情况数据不恢复】
            self::nextCallbackTimeDelay($project_id, $patient_card_id, $time);
        }

        return true;
    }

    /**
     * @apiName 更新 因修改住院时间/消费时间/治疗时间 影响的 有效到院信息问题
     * @apiGroup
     * @param $start_time
     * @param $end_time
     * @param $hospital
     * @author loujie
     */
    public static function updateToHospitalRecord($start_time, $end_time, $hospital = null)
    {
        if (!empty($start_time) && !empty($end_time)) {
            $end_time .= ' 23:59:59';
        } else {
            $start_time = date("Y-m-d");
            $end_time = $start_time . ' 23:59:59';
        }

        // 查询有效到院表
        $revisitingQuery = RevisitingRecord::find()
            ->where(['between', 'revisting_time', strtotime($start_time), strtotime($end_time)])
            ->andWhere(['type' => RevisitingRecord::TYPE_TREAT]);

        !empty($hospital) && $revisitingQuery->andWhere(['project_id' => ExcelService::getProject($hospital)]);
        $data = $revisitingQuery->asArray()->all();

        foreach ($data as $item) {
            self::updateTreatRecord($item['source_id']);
        }
    }

    /**
     * @apiName 查询治疗信息有没有修改,如果存在就修改
     * @apiGroup
     * @param $source_id 资源来源id
     * @param bool $delete 撤销真删除
     * @return bool
     * @throws \Throwable
     * @throws \yii\db\StaleObjectException
     * @author loujie
     */
    public static function updateTreatRecord($source_id, $delete = false)
    {
        $revisitingModel = RevisitingRecord::find()
            ->where(['type' => RevisitingRecord::TYPE_TREAT, 'source_id' => $source_id])
            ->one();
        $treatModel = UserTreatment::findOne($source_id);

        if (empty($revisitingModel)) {
            return false;
        }

        if ($revisitingModel->revisting_time == $treatModel->recently_clinic_time && !$delete) {
            return true;
        }

        #如果,旧的时间是有效 来院时间, 在当天查找其他的来替代
        if ($revisitingModel->is_sum == RevisitingRecord::IS_SUM_YES) {
            // 查询修改前最近一次住院信息
            $inHospital = InHospital::find()
                ->where([
                    'and',
                    ['project_id' => $revisitingModel->project_id],
                    ['card_id' => $revisitingModel->patient_card_id],
                    ['<', 'event_time', $revisitingModel->revisting_time]
                ])
                ->orderBy(['event_time' => SORT_DESC])
                ->asArray()
                ->one();

            $day_start = strtotime(date('Y-m-d', $inHospital['event_time']));
            $day_end = strtotime(date('Y-m-d', $inHospital['event_time']) . '23:59:59');
            $day_time = $revisitingModel->revisting_time;
            $is_day = ($day_time >= $day_start && $day_time <= $day_end) ? true : false;

            $firstQuery = RevisitingRecord::find()
                ->where([
                    'and',
                    ['project_id' => $revisitingModel->project_id],
                    ['patient_card_id' => $revisitingModel->patient_card_id]
                ])
                ->andWhere(['!=', 'type', RevisitingRecord::TYPE_OUT_HOSPITAL])
                ->andWhere(['!=', 'id', $revisitingModel->id]);

            if ($inHospital && $inHospital['type'] == InHospital::TYPE_IN && $is_day) {
                // 当天住院,且该条信息有效,重新从当天查找一条,有效数据
                $firstQuery->andWhere(['day_time' => $day_start]);
            } elseif ($inHospital && $inHospital['type'] == InHospital::TYPE_OUT && $is_day) {
                // 当天出院,从出院后信息中选取,有效
                $firstQuery->andWhere(['>', 'revisting_time', $day_end]);
            } else {
                // 没有住院数据,从当天挑一条有效数据
                $firstQuery->andWhere(['day_time' => strtotime(date('Y-m-d', $day_time))]);
            }

            $firstModel = $firstQuery->orderBy(['is_sum' => SORT_DESC])
                ->one();

            if ($firstModel && $firstModel->is_sum == RevisitingRecord::IS_SUM_NO) {
                $firstModel->is_sum = RevisitingRecord::IS_SUM_YES;
                $firstModel->save();
            }
        }

        if ($delete == true) {
            RevisitingRecord::deleteAll(['id' => $revisitingModel->id]);
            return true;
        }

        # 给修改后的,数据添加状态(是否统计)
        // 查询修改后,最近一次住院情况
        $is_hospital = self::isHospital($treatModel->project_id, $treatModel->patient_card_id, $treatModel->recently_clinic_time);

        $revisiting = RevisitingRecord::find()
            ->where([
                'and',
                ['project_id' => $revisitingModel->project_id],
                ['patient_card_id' => $revisitingModel->patient_card_id]
            ])
            ->andWhere(['!=', 'id', $revisitingModel->id])
            ->andWhere(['day_time' => strtotime(date('Y-m-d', $treatModel->recently_clinic_time))])
            ->andWhere(['is_sum' => RevisitingRecord::IS_SUM_YES])
            ->exists();

        if ($is_hospital || $revisiting) {
            $revisitingModel->is_sum = RevisitingRecord::IS_SUM_NO;
        } elseif (!$revisiting) {
            $revisitingModel->is_sum = RevisitingRecord::IS_SUM_YES;
        }

        $revisitingModel->revisting_time = $treatModel->recently_clinic_time;
        $revisitingModel->day_time = strtotime(date('Y-m-d', $treatModel->recently_clinic_time));
        $revisitingModel->save();
        return true;
    }

    /**
     * 更新用户,最新的上次到诊时间,来院次数
     * @param $project_id
     * @param $patient_card_id
     */
    public static function updateLastRevisitingTime($project_id, $patient_card_id, $time)
    {
        $userModel = User::find()
            ->select('id, last_revisiting_time')
            ->where([
                'and',
                ['project_id' => $project_id],
                ['status' => User::STATUS_ENABLE],
                ['patient_card_id' => $patient_card_id]
            ])
            ->one();

        if ($userModel) {
            // 来院次数
            $revisiting_count = RevisitingRecord::find()
                ->where([
                    'project_id' => $project_id,
                    'patient_card_id' => $patient_card_id,
                    'is_sum' => RevisitingRecord::IS_SUM_YES
                ])->count();

            $userModel->revisiting_count = $revisiting_count;

            if ($time > $userModel->last_revisiting_time) {
                $userModel->last_revisiting_time = $time;
                // 上次到诊时间> 下次复诊时间,清空下次复诊时间
                $userModel->last_revisiting_time > $userModel->next_revisiting_time ? $userModel->next_revisiting_time = 0 : '';
                $userModel->missed_days = ExcelService::getMissedDays($time);
            }

            $userModel->save();

        }
    }

    /**
     * 根据出院信息,查询今天之前最近一次入院信息,清空两次
     *
     *
     */
    public static function updateOutHospitalRecord($start_time = null, $end_time = null, $hospital = null)
    {
        $inHospitalQuery = InHospital::find()
            ->where([
                'type' => InHospital::TYPE_OUT
            ]);

        if (!empty($start_time) && !empty($end_time)) {

            $inHospitalQuery->andWhere([
                'between',
                'event_time',
                strtotime($start_time),
                strtotime($end_time . ' 23:59:59')
            ]);
        }

        !empty($hospital) && $inHospitalQuery->andWhere(['project_id' => ExcelService::getProject($hospital)]);

        $inHospitalArray = $inHospitalQuery->asArray()->all();

        foreach ($inHospitalArray as $item) {

            $day = strtotime(date('Y-m-d', $item['event_time']));
            // 查询最近一次入院时间,清除期间的
            $inHospitalModel = InHospital::find()
                ->where([
                    'project_id' => $item['project_id'],
                    'card_id' => $item['card_id'],
                ])
                ->andWhere(['<', 'event_time', $day])
                ->orderBy(['event_time' => SORT_DESC])
                ->one();

            if ($inHospitalModel && $inHospitalModel->type == InHospital::TYPE_IN) {

                // 清除该天,之后到出院前的所有 的有效到院
                $day_time = strtotime(date('Y-m-d', $inHospitalModel['event_time']) . ' 23:59:59');

                RevisitingRecord::updateAll(['is_sum' => RevisitingRecord::IS_SUM_NO], [
                    'and',
                    ['between', 'revisting_time', $day_time, $item['event_time']],
                    ['project_id' => $inHospitalModel->project_id],
                    ['patient_card_id' => $inHospitalModel->card_id]
                ]);
            }
        }
    }

    /**
     * @apiName  根据最近一次的消费数据来自动更新患者的诊室，更新诊室后原有的医助归属人和客户归属人清空，按系统流程分配给新的医助或客服归属人。
     * @apiGroup
     * @apiParam {Number}
     * @apiParam {String}
     * @author loujie
     */
    public static function updateUserClinic()
    {
        $hospitalList = [
            'hfy',
            'hfx',
            'sz',
            'nb',
            'cs',
            'nc',
            'wz',
            'nt',
            'gz',
            'hk',
            'hz',
            'fy'
        ];

        $limit = 10000;

        foreach ($hospitalList as $hospital) {

            RegistrationList::$pix = $hospital;
            SectionList::$pix = $hospital;
            Expense::$pix = $hospital;
            // 当天起始时间戳
            $startTIme = strtotime(date('Y-m-d'));

            // 对,消费记录进行排序
            $sql = Expense::find()
                ->select('e.card_id,s.clinic_id')
                ->alias('e')
                ->leftJoin(SectionList::tableName() . ' s', 'e.section = s.id')
                ->where([
                    'and',
                    ['>', 's.clinic_id', '0'],
                    ['>', 'e.created_at', $startTIme]
                ])
                ->orderBy(['e.day' => SORT_DESC, 'e.id' => SORT_DESC])
                ->limit($limit)
                ->createCommand()
                ->getRawSql();

            // 最后一次消费所在
            $result = Yii::$app->db3->createCommand("select *from  ($sql) as a group by a.card_id")->queryAll();
            // 获取用户诊疗号
            $cardIds = array_column($result, 'card_id');

            $userArray = User::find()
                ->select('id, patient_card_id as card_id, clinic_id')
                ->where([
                    'project_id' => ExcelService::getProject($hospital),
                    'status' => User::STATUS_ENABLE,
                    'patient_card_id' => $cardIds
                ])
                ->asArray()
                ->all();

            $userArray = array_column($userArray, null, 'card_id');

            foreach ($result as $item) {

                // 获取白友患者信息
                $userItem = $userArray[$item['card_id']] ?? [];

                if (!$userItem) {
                    continue;
                }

                // 判断最后一次消费所在诊室与白友诊室是否相同
                if ($item['clinic_id'] == $userItem['clinic_id']) {
                    continue;
                }

                // 如果存在不同,更新诊室,重新分配医助和客服归属人( 更换诊室比较少,单次更新)
                // 分配诊室后,分配跟换医助
                $assistantArray = ExcelService::getClinicRoom($hospital, $item['clinic_id']);

                if ($assistantArray) {
                    $transaction = Yii::$app->db->beginTransaction();
                    try {
                        $model = User::findOne($userItem['id']);
                        // 替换诊室
                        $model->clinic_id = $assistantArray['id'];
                        // 替换医助
                        $model->belong_assistant_id = isset($assistantArray['member']) ? $assistantArray['member'][array_rand($assistantArray['member'])] : 0;

                        // 原来的诊室信息
                        $by_clinic_nameArray = ExcelService::getClinicRoom($hospital, $userItem['clinic_id']);

                        $by_clinic_name = $by_clinic_nameArray['name'] ?? '';
                        $yy_clinic_name = $assistantArray['name'];

                        if ($model->save()) {
                            $operateModel = new OperateLog();
                            $operateModel->project_id = ExcelService::getProject($hospital);
                            $operateModel->action_type = OperateLog::ACTION_TYPE_PATIENT_UPDATE;
                            $operateModel->patient_user_id = $userItem['id'];
                            $operateModel->action_content = '系统自动根据最近消费记录，将当前患者所在诊室【' . $by_clinic_name . '】自动调整为诊室【' . $yy_clinic_name . '】';
                            $operateModel->operate_name = '系统定时任务';
                            $operateModel->save();

                            // 记录医助归属人
                            $allocationArray = [
                                'project_id' => $model->project_id,
                                'patient_card_id' => $model->patient_card_id,
                                'member_id' => $model->belong_assistant_id,
                                'current_profession_id' => 1
                            ];
                            ExcelService::allocationRecord($allocationArray);
                        }

                        $transaction->commit();
                    } catch (Exception $e) {
                        $transaction->rollBack();
                    }
                }
            }

        }


    }

    public static function repairCity()
    {
        $userData = User::find()
            ->select('id, city_id, address')
            ->where([
                'and',
                ['=', 'city_id', '0'],
                ['<>', 'address', '']
            ])
            ->asArray()
            ->all();

        foreach ($userData as $item) {
            // 解析城市
            $cityArray = ExcelService::dealCity($item['address']);

            if ($cityArray['city_id'] == 0) {
                continue;
            }

            User::updateAll(['city_id' => $cityArray['city_id'], 'city_name' => $cityArray['city_name']], ['id' => $item['id']]);
        }
    }

    /**
     * @apiName 修复消费记录诊室
     * @apiGroup
     * @apiParam {Number}
     * @apiParam {String}
     * @author loujie
     */
    public static function repairConsumptionClinic()
    {
        $projectArray = Project::find()
            ->select('id,name_sx')
            ->where(['status' => Project::STATUS_ENABLE])
            ->asArray()
            ->all();

        foreach ($projectArray as $project) {

            SectionList::$pix = $project['name_sx'];
            $clinicArray = SectionList::find()
                ->select('clinic_name, clinic_id')
                ->where(['>', 'clinic_id', 0])
                ->asArray()
                ->all();

            foreach ($clinicArray as $clinic) {

                echo $project['name_sx'] . ': ' . $clinic['clinic_name'];
                echo PHP_EOL;

                Consumption::updateAll(['clinic_id' => $clinic['clinic_id']], [
                    'project_id' => $project['id'],
                    'consulting_room' => $clinic['clinic_name']
                ]);
            }
        }
    }

    public static function repairConsumptionEmpty($project_id = 0)
    {

        echo '########空白诊室名修改########' . PHP_EOL;

        $query = Project::find()
            ->select('id,name_sx')
            ->where(['status' => Project::STATUS_ENABLE]);

        if (!empty($project_id)) {
            $query->andWhere(['id' => $project_id]);
        }

        $projectArray = $query
            ->asArray()
            ->all();

        foreach ($projectArray as $project) {

            // 查询需要改变的,消费记录表id
            $consumptionIds = Consumption::find()
                ->select('c.id, d.yiyuan_aid')
                ->alias('c')
                ->leftJoin(ConsumptionDetail::tableName() . ' d', 'c.id = d.consumption_id')
                ->where([
                    'and',
                    ['c.project_id' => $project['id']],
                    ['c.clinic_id' => 0],
                    ['c.consulting_room' => '']
                ])
                ->groupBy('c.id')
                ->asArray()
                ->all();

            // 查询是否有id
            SectionList::$pix = $project['name_sx'];
            Expense::$pix = $project['name_sx'];

            $ids = array_column($consumptionIds, 'yiyuan_aid');
            $consumptionList = array_column($consumptionIds, 'id', 'yiyuan_aid');

            if ($ids) {
                // 获取诊室,映射id
                $map = Expense::find()
                    ->select(['s.*', 'GROUP_CONCAT(e.id) AS expenseIds'])
                    ->alias('e')
                    ->leftJoin(SectionList::tableName() . ' s', 's.id = e.section')
                    ->where([
                        'and',
                        ['e.id' => $ids],
                        ['!=', 's.clinic_id', 0]
                    ])
                    ->groupBy('s.id')
                    ->asArray()
                    ->all();

                foreach ($map as $value) {

                    $expenseIds = explode(',', $value['expenseIds']);

                    echo $project['name_sx'] . '/' . $value['clinic_name'] . PHP_EOL;

                    // 获取消费聚合单,id
                    $detailIds = CommonHelper::link_array_key_value($expenseIds, $consumptionList);
                    Consumption::updateAll(
                        ['clinic_id' => $value['clinic_id'], 'consulting_room' => $value['clinic_name'], 'updated_at' => time()],
                        [
                            'and',
                            ['id' => $detailIds],
                            ['consulting_room' => '']
                        ]
                    );

                }


            }


//            SectionList::$pix = $project['name_sx'];
//            $clinicArray = SectionList::find()
//                ->select('clinic_name, clinic_id')
//                ->where(['>', 'clinic_id', 0])
//                ->asArray()
//                ->all();
//
//            foreach ($clinicArray as $clinic) {
//
//                echo $project['name_sx'] . ': ' .$clinic['clinic_name'];
//                echo PHP_EOL;
//
//                Consumption::updateAll(['clinic_id' => $clinic['clinic_id']],[
//                    'project_id' => $project['id'],
//                    'consulting_room' => $clinic['clinic_name']
//                ]);
//            }
        }


    }

    /**
     *修复医助归属人
     */
    public static function repairAssistantMemberId()
    {
        // 查询医助归属人与岗位的问题
        $assistantMemberInfo = User::find()
            ->select(['a.belong_assistant_id', 'a.project_id', 'c.project_id as attr_project_id', 'b.realname', 'b.`status`', 'c.profession_id'])
            ->alias('a')
            ->leftJoin(Member::tableName() . ' b', 'b.id = a.belong_assistant_id')
            ->leftJoin(Attr::tableName() . ' c', 'c.member_id = a.belong_assistant_id')
            ->where(['>', 'a.belong_assistant_id', 0])
            ->andWhere(['!=', 'c.profession_id', Profession::ID_MEDICAL])
            ->groupBy('a.belong_assistant_id, a.project_id')
            ->asArray()
            ->all();

        foreach ($assistantMemberInfo as $value) {
            self::allotAssistantMemberId($value['belong_assistant_id']);
        }

    }

    /**
     * 重新分配医助归属人
     * @param $assistantMemberId 需要修复的原医助归属人id
     */
    public static function allotAssistantMemberId($assistantMemberId)
    {
        $data = User::find()
            ->select('id, clinic_id, project_id')
            ->where([
                'belong_assistant_id' => $assistantMemberId,
                'status' => User::STATUS_ENABLE
            ])
            ->asArray()
            ->all();

        $projectInfo = Project::find()
            ->select('id, name_sx')
            ->where(['status' => Project::STATUS_ENABLE])
            ->asArray()
            ->all();

        $projectInfo = array_column($projectInfo, 'name_sx', 'id');

        foreach ($data as $value) {
            $hospital = $projectInfo[$value['project_id']];
            $assistantMemberArray = ExcelService::getClinicRoom($hospital, $value['clinic_id']);
            if ($assistantMemberArray && $assistantMemberArray['count'] > 0) {
                $index = mt_rand(0, $assistantMemberArray['count'] - 1);
                $model = User::findOne($value['id']);
                $model->belong_assistant_id = $assistantMemberArray['member'][$index];

                if ($model->save()) {
                    $allocationArray = [
                        'project_id' => $model->project_id,
                        'patient_card_id' => $model->patient_card_id,
                        'member_id' => $model->belong_assistant_id,
                        'current_profession_id' => 1
                    ];
                    ExcelService::allocationRecord($allocationArray);
                }
            }
        }
    }

    /**
     *修复客服归属人
     */
    public static function repairBelongMemberId()
    {
        // 查询客服归属人与岗位问题
        $belongMemberInfo = User::find()
            ->select(['a.belong_member_id', 'a.project_id', 'c.project_id as attr_project_id', 'b.realname', 'b.`status`', 'c.profession_id'])
            ->alias('a')
            ->leftJoin(Member::tableName() . ' b', 'b.id = a.belong_member_id')
            ->leftJoin(Attr::tableName() . ' c', 'c.member_id = a.belong_member_id')
            ->where(['>', 'a.belong_member_id', 0])
            ->andWhere(['not in', 'c.profession_id', [Profession::ID_CUSTOMER, Profession::ID_CUSTOMER_LEADER]])
            ->groupBy('a.belong_member_id, a.project_id')
            ->asArray()
            ->all();

        foreach ($belongMemberInfo as $value) {
            self::allotBelongMemberId($value['belong_member_id']);
        }

    }

    // 重新分配客服
    public static function allotBelongMemberId($belongMemberId)
    {
        $data = User::find()
            ->select('id, clinic_id, project_id')
            ->where([
                'belong_member_id' => $belongMemberId,
                'status' => User::STATUS_ENABLE
            ])
            ->asArray()
            ->all();

        $projectInfo = Project::find()
            ->select('id, name_sx')
            ->where(['status' => Project::STATUS_ENABLE])
            ->asArray()
            ->all();

        $projectInfo = array_column($projectInfo, 'name_sx', 'id');

        foreach ($data as $value) {
            $hospital = $projectInfo[$value['project_id']];
            $belongMemberArray = ExcelService::getCustomerService($hospital, $value['clinic_id']);
            if ($belongMemberArray && $belongMemberArray['count'] > 0) {
                $index = mt_rand(0, $belongMemberArray['count'] - 1);
                $model = User::findOne($value['id']);
                $model->belong_member_id = $belongMemberArray['member'][$index];
                $model->belong_member_name = $belongMemberArray['realname'][$index];

                if ($model->save()) {
                    $allocationArray = [
                        'project_id' => $model->project_id,
                        'patient_card_id' => $model->patient_card_id,
                        'member_id' => $model->belong_member_id,
                        'current_profession_id' => 2
                    ];
                    ExcelService::allocationRecord($allocationArray);
                }
            }
        }
    }

    /**
     * 修复流失数据
     * 初次挂号时间<今天,来院次数<2，消费金额<=流失状态设置的金额，患者就诊类型修改为：流失。 【一次性脚本】
     */
    public static function repairLoss()
    {
        // 项目
        $array = User::find()
            ->select([
                'a.id',
                'a.outpatient_type_id',
                'a.patient_card_id',
                'a.project_id',
                'sum(b.reality_money) as money'
            ])
            ->alias('a')
            ->leftJoin(Consumption::tableName() . ' b', 'b.project_id = a.project_id and b.patient_card_id = a.patient_card_id')
            ->where([
                'and',
                ['not in' ,'a.project_id' ,[13,14,11]],
                ['!=','a.outpatient_type_id',1004],
                ['<', 'a.reg_time', time()],
                ['<', 'a.revisiting_count', 2],
                ['a.status' => User::STATUS_ENABLE],
            ])
            ->groupBy('a.patient_card_id, a.project_id')
            ->having(['<', 'money', 500])
//            ->createCommand()
//            ->getRawSql();
            ->asArray()
            ->all();


        $result = [];

        foreach ($array as $value) {
            $result[$value['project_id']][] = $value['patient_card_id'];
        }

        foreach ($result as $project_id => $patientCardArray) {
            User::updateAll(['outpatient_type_id' => 1002, 'updated_at' => time()],['project_id' => $project_id, 'patient_card_id' => $patientCardArray]);
        }

        return true;

    }

    /**
     * 上次到诊日期为当天，下次回访日期也为当天，下次回访日期延后一周【撤销的治疗等情况数据不恢复】
     *
     * @param $project_id
     * @param $patient_card_id
     * @param $time
     */
    public static function nextCallbackTimeDelay($project_id, $patient_card_id, $time)
    {
        $day = strtotime(date('Y-m-d', $time));
        $today = strtotime(date('Y-m-d', time()));
        if ($day == $today) { // 时间是今天
            $patientUser = User::findOne(['project_id' => $project_id, 'patient_card_id' => $patient_card_id]);
            if ($patientUser && strtotime(date('Y-m-d', $patientUser->kf_next_callback_time)) == $today) {// 且客服下次回访时间为今天
                User::updateAll(
                    ['kf_next_callback_time' => ($today + 7*86400), 'updated_at' => time()],
                    ['project_id' => $project_id, 'patient_card_id' => $patient_card_id]
                );
            }
            if ($patientUser && strtotime(date('Y-m-d', $patientUser->yz_next_callback_time)) == $today) {// 且医助下次回访时间为今天
                User::updateAll(
                    ['yz_next_callback_time' => ($today + 7*86400), 'updated_at' => time()],
                    ['project_id' => $project_id, 'patient_card_id' => $patient_card_id]
                );
            }
        }
    }

    /**
     * 记录下每天
     * @param $params
     * @return void
     */
    public  function byImage($params) :string
    {
        // 诊室名称
        $clinic_name = $params['name'];
        $project_id = $params['project_id'];

       $exists = ClinicName::find()
            ->where([
                'project_id' => $project_id,
                'clinic_name' => $clinic_name
            ])
            ->exists();

        if (!$exists) {
            $model = new ClinicName();
            $model->clinic_name = $clinic_name;
            $model->project_id = $project_id;
            $model->saveAndCheckResult();
       }

       // 插入传输图片记录
       $logModel = new  ImageLog();
       $logModel->clinic_name = $clinic_name;
       $logModel->saveAndCheckResult();
       return "success";
    }

    /**
     * 发送 图片未同步通知
     * @return void
     */
    public function sendImageMessage()
    {
        // 所有诊室
        $clinicArray = ClinicName::find()->select('clinic_name,project_id')->indexBy("clinic_name")->asArray()->all();

        $startTIme = strtotime(date("Y-m-d"));
        $endTime = $startTIme + 86399;
        // 今天发送过图片的诊室
        $todayClinicName = ImageLog::find()
            ->select("clinic_name")
            ->where(['between','created_at',$startTIme,$endTime])
            ->asArray()
            ->all();
        // 取差集,获取没有同步图片的诊室
        $noSend = array_diff(array_keys($clinicArray),$todayClinicName);

        $sendArray = [];

        // 根据差集
        foreach ($noSend as $value) {
            $clinicInfo = $clinicArray[$value];
            $sendArray[$clinicInfo['project_id']][] = $clinicInfo['clinic_name'];
        }

        foreach ($sendArray as $project_id => $sendInfo) {
            $message = implode(",",$sendInfo) . "等诊室图片今天没有上传";
            ExcelService::sendMessage($message,Yii::$app->params['DING_TOKEN'], Yii::$app->params['DING_KEY'],$project_id);
        }
    }

    /**
     * 同步工作
     * @return void
     */
    public function repairWorkCity($project_id = 0)
    {
        if (!empty($project_id)) {
           $userQuery = User::find()
                ->select('patient_card_id, id')
                ->where([
                    'project_id' => $project_id,
                    'work_address' => ""
                ])
               ->indexBy('patient_card_id')
               ->asArray();

           foreach ($userQuery->batch('100') as $valueArray) {
                // 查询
               $patientCard = array_column($valueArray,'patient_card_id');
               $historyAddress = HistoryAddress::find()
                   ->select('work_unit,card_id')
                   ->where(['!=','work_unit',''])
                   ->andWhere(['card_id' => $patientCard])
                   ->asArray()
                   ->all();

                $updateArray = [];
                // 更新历年消费,和更新时间
                foreach ($historyAddress as $addressInfo) {
                    $id = $valueArray[$addressInfo['card_id']]['id'];
                    $updateArray[] = \Yii::$app->db->createCommand()->update(User::tableName(),['work_address' => $addressInfo['work_unit'],'updated_at' => time()],['id' => $id])->getRawSql();
                }
                if (!empty($updateArray)) {
                    $sql = implode(";\n", $updateArray) . ';';
                    \Yii::$app->db->createCommand($sql)->execute();
                }
           }
        }
    }

    /**
     * 项目下拉列表
     * @return array
     */
    public function projectList()
    {
        return Project::find()
            ->select('id as value , name as label')
            ->where(['status' => Project::STATUS_ENABLE])
            ->asArray()
            ->all();
    }

    /**
     * 患者详情
     * @property string $patient_card_id 诊疗号
     * @property string $name 患者姓名
     * @property string $birthday 生日
     * @property int $age 年龄
     * @property string $wx_name 微信号
     * @property string $nickname 微信昵称
     * @property int $gender 性别[0:未知;1:男;2:女]
     * @property string $mobile 手机号
     */
    public function patientInfo($params)
    {
        $project_id = $params['project_id'] ?? 0;
        $patientCard = $params['patientCard'];
        $patientInfo = User::find()
            ->alias('a')
            ->leftJoin(PatientDiseases::tableName() . ' b','a.patient_diseases_id = b.id')
            ->leftJoin(SourceChannel::tableName() . ' c','c.id = a.source_channel_id')
            ->leftJoin(Clinic::tableName(). ' d','d.id = a.clinic_id')
            ->select([
                'a.patient_card_id', 'a.name', 'a.birthday', 'a.age', 'a.wx_name', 'a.nickname', 'a.gender', 'a.mobile',"from_unixtime(a.reg_time) as reg_time",'a.work_address','a.outpatient_type_id',
                "from_unixtime(a.last_revisiting_time) as last_revisiting_time",
                'b.diseases_name',
                'c.source_name',
                'd.name as clinic_name'
            ])
            ->where(['a.patient_card_id' => $patientCard, 'a.project_id' => $project_id , 'a.status' => [User::STATUS_ENABLE, User::STATUS_INVALID]])
            ->asArray()
            ->one();
        if (empty($patientInfo)) {
            return CommonHelper::api(['422','诊疗号不存在']);
        }
        return $patientInfo;
    }


    /**
     * 删除错误诊疗号相关 数据
     * @param $params
     * @return array|bool|mixed
     */
    public function delPatientData($params)
    {
        $project_id = $params['project_id'] ?? 0;
        $patientCard = $params['patientCard'];
        $patientModel = User::find()->select('id, status')->where(['patient_card_id' => $patientCard, 'project_id' => $project_id])->one();
        if (empty($patientModel)) {
            return CommonHelper::api(['422','诊疗号不存在']);
        }
        // 获取项目简称
        $project_name_sx = Project::find()->select('name_sx')->where(['id' => $project_id])->scalar();
        RegistrationList::$pix = $project_name_sx;
        Expense::$pix = $project_name_sx;
        // 删除诊疗号涉及到的相关表 删后恢复不易,请慎重考虑
        /*
             by_patient_expense_cs  消费表 (cs 长沙 选择不同项目数据库)
             by_registration_list_wz  挂号表 (wz 温州 选择不同项目数据库)
             by_patient_consumption_detail  消费详情(当天每个药品消费记录)
             by_patient_consumption   消费合计(每次消费总金额)
             by_patient_user  患者表
             by_patient_hospital_record 患者住/出院记录表
             by_patient_revisiting_record  患者消费、治疗、来院记录表(用来统计有效到诊次数的)
             by_clinic_user_treatment 治疗室 - 患者治疗信息表
             by_clinic_user_treatment_detail  治疗室 - 患者治疗信息详情表
         */
        // 删除诊疗号
        User::updateAll(['status' => User::STATUS_DELETE, 'updated_at' => time()],['patient_card_id' => $patientCard, 'project_id' => $project_id]);
        // 删除住院/出院记录
        HospitalRecord::deleteAll(['patient_card_id' => $patientCard, 'project_id' => $project_id]);
        // 删除患者消费、治疗、来院记录表
        RevisitingRecord::deleteAll(['patient_card_id' => $patientCard, 'project_id' => $project_id]);
        // 删除治疗信息
        UserTreatment::deleteAll(['patient_card_id' => $patientCard, 'project_id' => $project_id]);
        // 删除治疗详细表
        UserTreatmentDetail::deleteAll(['patient_card_id' => $patientCard, 'project_id' => $project_id]);
        // 删除消费表
        Consumption::deleteAll(['patient_card_id' => $patientCard, 'project_id' => $project_id]);
        // 删除消费详情表
        ConsumptionDetail::deleteAll(['patient_card_id' => $patientCard, 'project_id' => $project_id]);
        // 删除中转库消费表
        Expense::deleteAll(['card_id' => $patientCard]);
        // 删除中转站挂号表
        RegistrationList::updateAll(['state' => 300],['card_id' => $patientCard]);
        return true;
    }





}
